package QuizPackage;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;

import social.SocialDBC;


public class QuizDBC {
	//Database info
	static String  account = "ccs108eplotkin";
	static String password = "umaiquee";
	static String server="mysql-user-master.stanford.edu";
	static String database = "c_cs108_eplotkin";
		
	//MySQL link variables
	private Connection con;
	private Statement stmt;
	
	private static Object lock = new Object();
	
	/**
	 * Constructor.  Establishes a connection to the database.
	 */
	public QuizDBC()
	{
		try{
			Class.forName("com.mysql.jdbc.Driver"); 
			con = DriverManager.getConnection("jdbc:mysql://" + server, account ,password);
			stmt = con.createStatement();
			stmt.executeQuery("USE " + database);
		}catch (SQLException e) { 	
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Gets the ResultSet containing the info for the quizID specified.
	 */
	public ResultSet getQuiz(int quizID)
	{
		ResultSet rs=null;
		try {
			rs = stmt.executeQuery("SELECT * FROM quizzes WHERE quizid="+quizID+";");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	/**
	 * Gets the ResultSet containing the info for all of the quizzes
	 * in the given tableName that are associated with the given quizID.
	 */
	public ResultSet getQuestions(String tableName,int quizID)
	{
		ResultSet rs=null;
		try {
			rs = stmt.executeQuery("SELECT * FROM "+tableName+" WHERE quizid="+quizID+";");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	/**
	 * Calls getQuizList with a default of ascending order.
	 */
	public ArrayList<Integer> getQuizList()
	{
		return getQuizList(true);
	}
	
	/**
	 * Returns a list of all quizzes stored in the database,
	 * identifying them by their numerical ID.  The ID can be
	 * used to construct a Quiz object or to get more info about
	 * the Quiz using getStringByID.  Can specify that the list
	 * be in ascending(true) or descending(false) order.  A call to
	 * getQuizList with no parameter will default to ascending order.
	 */
	public ArrayList<Integer> getQuizList(boolean ascending)
	{
		ArrayList<Integer> ids = new ArrayList<Integer>();
		ResultSet rs=null;
		try {
			String query = "SELECT quizid FROM quizzes ORDER BY quizid";
			if(!ascending) query += " DESC";
			query += ";";
			rs = stmt.executeQuery(query);
			while(rs.next())
			{
				ids.add(Integer.parseInt(rs.getString("quizid")));
			}
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ids;
	}
	
	public ArrayList<Integer> getQuizListByCategory(String category)
	{
		ArrayList<Integer> ids = new ArrayList<Integer>();
		ResultSet rs=null;
		try {
			String query = "SELECT quizid FROM quizzes WHERE category=\""+category+"\";";
			rs = stmt.executeQuery(query);
			while(rs.next())
			{
				ids.add(Integer.parseInt(rs.getString("quizid")));
			}
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ids;
	}
	
	public ArrayList<Integer> getQuizListByTag(String tag)
	{
		ArrayList<Integer> ids = new ArrayList<Integer>();
		ResultSet rs=null;
		try {
			String query = "SELECT quizid FROM tags WHERE tag=\""+tag+"\";";
			rs = stmt.executeQuery(query);
			while(rs.next())
			{
				ids.add(Integer.parseInt(rs.getString("quizid")));
			}
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ids;
	}
	
	public ArrayList<Integer> getQuizListByCreator(String creator) {
		ArrayList<Integer> quizList = new ArrayList<Integer>();
		try {
			String query = String.format("SELECT * FROM quizzes WHERE creator=\"%s\" ORDER BY quizid DESC;", creator);
			ResultSet rs = stmt.executeQuery(query);
			
			while (rs.next()) {
				quizList.add(Integer.parseInt(rs.getString("quizid")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return quizList;
	}
	
	/**
	 * Gets a value from the quizzes table corresponding to the
	 * provided quizid.  The value retrieved depends on the String
	 * value passed into it.
	 * @param value Valid parameters "name","quizid","creator","description"
	 */
	public String getStringByID(int id,String value)
	{
		ResultSet rs = null;
		try{
			rs = stmt.executeQuery("SELECT "+value+" FROM quizzes WHERE quizid="+id+";");
			if(rs.next()) return rs.getString(value);
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Enters the result described by the parameters passed to it into the
	 * results table.
	 * @param quizid Quiz taken
	 * @param userID Username of quiz taker
	 * @param score Score received
	 * @param total Total point possible
	 * @param length How long the user took to take the test
	 * @param timetaken When the test was taken.
	 */
	public void enterResult(int quizid,String userID,int score,int total,int length,long timetaken)
	{
		try {
			ResultSet rs = stmt.executeQuery("SELECT * FROM quizzes WHERE quizid="+quizid+";");
			rs.next();
			int pop = Integer.parseInt(rs.getString("popularity"));
			stmt.executeUpdate("UPDATE quizzes SET popularity="+(pop+1)+" WHERE quizid="+quizid+";");
			
			stmt.executeUpdate("INSERT INTO results VALUES ("+quizid+",\""+userID+"\","+score+","+total+","+length+","+timetaken+");");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public ArrayList<Integer> getResults(String userID) {
		ArrayList<Integer> resultList = new ArrayList<Integer>();
		try {
			String query = String.format("SELECT * FROM results WHERE userID=\"%s\" ORDER BY timetaken DESC;", userID);
			ResultSet rs = stmt.executeQuery(query);
			
			while (rs.next()) {
				resultList.add(Integer.parseInt(rs.getString("quizid")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return resultList;
	}
	
	public int getNumTaken() {
		int num = 0;
		try {
			String query = String.format("SELECT * FROM results");
			ResultSet rs = stmt.executeQuery(query);
			
			while (rs.next()) {
				num++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return num;
	}
	
	public ResultSet getResultSet(String userID) {
		ResultSet rs = null;
		try {
			String query = String.format("SELECT * FROM results WHERE userID=\"%s\" ORDER BY timetaken DESC;", userID);
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return rs;
	}
	
	public ResultSet getFriendResults(ArrayList<String> friendList) {		
		ResultSet rs = null;
		
		if (friendList.size()==0) {
			return null;
		}
		try {
			String query = "SELECT * FROM results WHERE ";
			for (int i=0; i<friendList.size(); i++) {
				query += String.format("userID=\"%s\" ", friendList.get(i));
			}
			query += "ORDER BY timetaken DESC;";
			//System.out.println(query);
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return rs;
	}
	
	public ResultSet getTopPerformers(int quizid)
	{
		ResultSet rs = null;
		try{
			rs = stmt.executeQuery("SELECT * FROM results WHERE quizid="+quizid+" ORDER BY score DESC, length ASC;");
		} catch (SQLException e){
			e.printStackTrace();
		}
		return rs;
	}
	
	public ArrayList<Integer> getMostPopular()
	{
		ArrayList<Integer> result = new ArrayList<Integer>();
		ResultSet rs = null;
		try{
			rs = stmt.executeQuery("SELECT * FROM quizzes ORDER BY popularity DESC;");
			while(rs.next())
			{
				result.add(Integer.parseInt(rs.getString("quizid")));
			}
		} catch (SQLException e){
			e.printStackTrace();
		}
		return result;
	}
	
	public ResultSet getResults(int quizid)
	{
		ResultSet rs = null;
		try{
			rs = stmt.executeQuery("SELECT * FROM results WHERE quizid="+quizid+" ORDER BY timetaken DESC;");
		} catch (SQLException e){
			e.printStackTrace();
		}
		return rs;
	}
	
	/**
	 * Enters the quiz described by the parameters passed to it into the quizzes
	 * table.  QuizID is generated by the program, based on the number of quizzes
	 * already stored.  REturns the quizID assigned to the quiz.
	 */
	public int addQuiz(String name,String creator,boolean random,boolean singlepage,boolean correction,String description,String category,boolean practice)
	{
		int isRandom = (random) ? 1:0;
		int isSingle = (singlepage) ? 1:0;
		int isCorrection = (correction) ? 1:0;
		int isPractice = (practice) ? 1:0;
		int quizid = -1;
		synchronized(lock)
		{
			ArrayList<Integer> list = getQuizList(false);
			if(list.size()==0) quizid = 0;
			else quizid = list.get(0) + 1;
			try {
				//System.out.println("INSERT INTO quizzes VALUES (\""+name+"\","+quizid+",\""+creator+"\","+isRandom+","+isSingle+","+isCorrection+",\""+description+"\",\""+category+"\","+isPractice+", 0);");
				stmt.executeUpdate("INSERT INTO quizzes VALUES (\""+name+"\","+quizid+",\""+creator+"\","+isRandom+","+isSingle+","+isCorrection+",\""+description+"\",\""+category+"\","+isPractice+", 0);");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return quizid;
	}
	
	/**
	 * Calls stmt.executeUpdate(update) to edit
	 * a table in the database.
	 * @param update
	 */
	public void editTable(String update)
	{
		try {
			stmt.executeUpdate(update);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public ResultSet getTags(int quizid)
	{
		ResultSet rs = null;
		try{
			rs = stmt.executeQuery("SELECT * FROM tags WHERE quizid="+quizid+";");
		} catch (SQLException e){
			e.printStackTrace();
		}
		return rs;
	}
	
	public void deleteQuiz(int quizid)
	{
		try {
			stmt.executeUpdate("DELETE FROM quizzes WHERE quizid="+quizid+";");
			
			for (int i=0; i<Quiz.questionTypes.length; i++) {
				String update = String.format("DELETE FROM %s WHERE quizid="+quizid+";", Quiz.questionTypes[i]);
				stmt.executeUpdate(update);
			}
			
			stmt.executeUpdate("DELETE FROM results WHERE quizid="+quizid+";");
			stmt.executeUpdate("DELETE FROM tags WHERE quizid="+quizid+";");
			stmt.executeUpdate("DELETE FROM ratings WHERE quizid="+quizid+";");
			SocialDBC db = new SocialDBC();
			db.removeChallenges(quizid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void addRating(String username,int rating,int quizid)
	{
		try {
			ResultSet rs = stmt.executeQuery("SELECT * FROM ratings WHERE username=\""+username+"\" AND quizid=\""+quizid+"\";");
			if(rs.next()) stmt.executeUpdate("UPDATE ratings SET rating="+rating+" WHERE username=\""+username+"\" AND quizid=\""+quizid+"\";");
			else stmt.executeUpdate("INSERT INTO ratings VALUES (\""+username+"\","+quizid+","+rating+");");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public String averageRating(int quizid)
	{
		int count = 0;
		int points = 0;
		ResultSet rs;
		try{
			rs = stmt.executeQuery("SELECT * FROM ratings WHERE quizid="+quizid+";");
			while(rs.next())
			{
				count++;
				points += Integer.parseInt(rs.getString("rating"));
			}
		} catch (SQLException e){
			e.printStackTrace();
		}
		if(count == 0) return "-";
		double averageRating = ((double)points)/count;
		DecimalFormat df = new DecimalFormat("#.##");
		return df.format(averageRating);
	}
	
	public void removeResults(String username)
	{
		try {
			stmt.executeUpdate("DELETE FROM results WHERE userID=\""+username+"\";");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void removeQuizResults(int quizid)
	{
		try {
			stmt.executeUpdate("DELETE FROM results WHERE quizid=\""+quizid+"\";");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
